﻿using Bot.Domain;
using LumenWorks.Framework.IO.Csv;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Bot.Infrastructure
{
    public interface ITransferData
    {
        Stream GetStream(DataTable table);
        DataTable GetData(Stream stream);
        DataTable GetData(string path);
        string SaveData(DataTable table, string path);
    }
    public class CsvTransferData : ITransferData
    {
        private Encoding _encode;
        public CsvTransferData()
        {
            this._encode = Encoding.GetEncoding("utf-8");
        }

        public Stream GetStream(DataTable table)
        {
            StringBuilder sb = new StringBuilder();
            if (table != null && table.Columns.Count > 0 && table.Rows.Count > 0)
            {
                //把列名增加到表中
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    if (i > 0)
                    {
                        sb.Append(",");
                    }
                    if (table.Columns[i] != null)
                    {
                        sb.Append("\"").Append(table.Columns[i].ColumnName.ToString().Replace("\"", "\"\"")).Append("\"");
                    }
                }
                sb.Append("\n");

                foreach (DataRow item in table.Rows)
                {
                    for (int i = 0; i < table.Columns.Count; i++)
                    {
                        if (i > 0)
                        {
                            sb.Append(",");
                        }
                        if (item[i] != null)
                        {
                            sb.Append("\"").Append(item[i].ToString().Replace("\"", "\"\"")).Append("\"");
                        }
                    }
                    sb.Append("\n");
                }
            }
            MemoryStream stream = new MemoryStream(_encode.GetBytes(sb.ToString()));
            return stream;
        }

        public DataTable GetData(Stream stream)
        {
            using (stream)
            {
                using (StreamReader input = new StreamReader(stream, _encode))
                {
                    using (CsvReader csv = new CsvReader(input, false))
                    {
                        DataTable dt = new DataTable();
                        int columnCount = csv.FieldCount;                       
                        int index = 0;
                        
                        while (csv.ReadNextRecord())
                        {
                            if (index == 0)
                            {
                                for (int i = 0; i < columnCount; i++)
                                {
                                    //dt.Columns.Add("col" + i.ToString());
                                    dt.Columns.Add(csv[i].ToString()); 
                                }
                                index++;
                            }
                            else
                            {
                                DataRow dr = dt.NewRow();
                                for (int i = 0; i < columnCount; i++)
                                {
                                    if (!string.IsNullOrWhiteSpace(csv[i]))
                                    {
                                        dr[i] = csv[i];
                                    }
                                }
                                dt.Rows.Add(dr);
                            }
                            
                        }
                        return dt;
                    }

                }
            }
        }

        public string SaveData(DataTable table, string path)
        {
            using(Stream sm = GetStream(table))
            {
                int numBytesToRead = (int)sm.Length;
                byte[] buffer = new byte[numBytesToRead];
                sm.Read(buffer, 0, buffer.Length);
                // 把 byte[] 写入文件   
                using (FileStream fs = new FileStream(path, FileMode.Create))
                {
                    using (BinaryWriter bw = new BinaryWriter(fs))
                    {
                        bw.Write(buffer);
                        return fs.Name;
                    }
                }
            }
        }
        public DataTable GetData(string path)
        {
            FileStream stream = new FileStream(path, FileMode.Open, FileAccess.Read);
            return GetData(stream);
        }
    }
    public abstract class ExcelTransferData : ITransferData
    {
        protected IWorkbook _workBook;

        public virtual Stream GetStream(DataTable table)
        {
            //var sheet = _workBook.CreateSheet();
            //if (table != null)
            //{
            //    var rowCount = table.Rows.Count;
            //    for (int i = 0; i < table.Rows.Count; i++)
            //    {
            //        var row = sheet.CreateRow(i);
            //        for (int j = 0; j < table.Columns.Count; j++)
            //        {
            //            var cell = row.CreateCell(j);
            //            if (table.Rows[i][j] != null)
            //            {
            //                cell.SetCellValue(table.Rows[i][j].ToString());
            //            }
            //        }
            //    }
            //}
            //MemoryStream ms = new MemoryStream();
            //_workBook.Write(ms);
            //return ms;

            throw new Exception();
        }

        public virtual DataTable GetData(Stream stream)
        {
            //using (stream)
            //{
            //    var sheet = _workBook.GetSheetAt(0);
            //    if (sheet != null)
            //    {
            //        var headerRow = sheet.GetRow(0);
            //        DataTable dt = new DataTable();
            //        int columnCount = headerRow.Cells.Count;
            //        for (int i = 0; i < columnCount; i++)
            //        {
            //            //dt.Columns.Add("col_" + i.ToString());
            //            dt.Columns.Add(headerRow.GetCell(i).ToString());
            //        }
            //        var row = sheet.GetRowEnumerator();
            //        int index = 0;
            //        while (row.MoveNext())
            //        {
            //            if (index == 0)
            //            {
            //                //跳出第一行
            //                index++;
            //                continue;
            //            }
            //            var dtRow = dt.NewRow();
            //            var excelRow = row.Current as IRow;
            //            for (int i = 0; i < columnCount; i++)
            //            {
            //                var cell = excelRow.GetCell(i);

            //                if (cell != null)
            //                {
            //                    dtRow[i] = GetValue(cell);
            //                }
            //            }
            //            dt.Rows.Add(dtRow);
            //        }
            //        return dt;
            //    }
            //}

            //return null;

            return NPOIHelper.ImportExceltoDt(stream);
        }

        public virtual string SaveData(DataTable table, string path)
        {
            using (Stream sm = GetStream(table))
            {
                int numBytesToRead = (int)sm.Length;
                byte[] buffer = new byte[numBytesToRead];
                sm.Read(buffer, 0, buffer.Length);
                // 把 byte[] 写入文件   
                using (FileStream fs = new FileStream(path, FileMode.Create))
                {
                    using (BinaryWriter bw = new BinaryWriter(fs))
                    {
                        bw.Write(buffer);
                        return fs.Name;
                    }
                }
            }
        }

        public virtual DataTable GetData(string path)
        {
            //FileStream stream = new FileStream(path, FileMode.Open, FileAccess.Read);
            //return GetData(stream);

            return NPOIHelper.ImportExceltoDt(path);
        }

        private object GetValue(ICell cell)
        {
            object value = null;
            switch (cell.CellType)
            {
                case CellType.Blank:
                    break;
                case CellType.Boolean:
                    value = cell.BooleanCellValue ? "1" : "0"; break;
                case CellType.Error:
                    value = cell.ErrorCellValue; break;
                case CellType.Formula:
                    value = "=" + cell.CellFormula; break;
                case CellType.Numeric:
                    value = cell.NumericCellValue.ToString(); break;
                case CellType.String:
                    value = cell.StringCellValue; break;
                case CellType.Unknown:
                    break;
            }
            return value;
        }

    }
    public class XlsTransferData : ExcelTransferData
    {
        public override Stream GetStream(DataTable table)
        {
            return NPOIHelper.ExportXls(table, "1");
        }

        public override DataTable GetData(Stream stream)
        {
            base._workBook = new HSSFWorkbook(stream);
            return base.GetData(stream);
        }
    }
    public class XlsxTransferData : ExcelTransferData
    {

        public override Stream GetStream(DataTable table)
        {
            NPOIMemoryStream ms = NPOIHelper.ExportXlsx(table, "1");
            ms.IsClose = true;
            return ms;
        }

        public override DataTable GetData(Stream stream)
        {
            base._workBook = new XSSFWorkbook(stream);
            return base.GetData(stream);
        }
        
    }
    public enum DataFileType
    {
        CSV,
        XLS,
        XLSX
    }
    public class TransferDataFactory
    {
        public static ITransferData GetUtil(string fileName)
        {
            var array = fileName.Split('.');
            var dataType = (DataFileType)System.Enum.Parse(typeof(DataFileType), array[array.Length - 1], true);
            return GetUtil(dataType);
        }

        public static ITransferData GetUtil(DataFileType dataType)
        {
            switch (dataType)
            {
                case DataFileType.CSV: return new CsvTransferData();
                case DataFileType.XLS: return new XlsTransferData();
                case DataFileType.XLSX: return new XlsxTransferData();
                default: return new CsvTransferData();
            }
        }

    }
}
